
<!DOCTYPE html><html lang="zh-CN">

<head>
  <meta charset="utf-8">
  <meta name="hexo-theme" content="https://github.com/xaoxuu/hexo-theme-stellar/tree/1.29.1" theme-name="Stellar" theme-version="1.29.1">
  
  <meta name="generator" content="Hexo 7.1.1">
  <meta http-equiv='x-dns-prefetch-control' content='on' />
  
  <meta name="renderer" content="webkit">
  <meta name="force-rendering" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1">
  <meta name="HandheldFriendly" content="True" >
  <meta name="apple-mobile-web-app-capable" content="yes">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="theme-color" media="(prefers-color-scheme: dark)" content="#000">
  <meta name="theme-color" content="#f9fafb">
  
  <title>SQL练习 - Sentry's Blog</title>

  
    <meta name="description" content="SQL练习题">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL练习">
<meta property="og:url" content="https://coclong.gitee.io/2023/08/12/practice/SQL%E7%BB%83%E4%B9%A0/index.html">
<meta property="og:site_name" content="Sentry&#39;s Blog">
<meta property="og:description" content="SQL练习题">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://gcore.jsdelivr.net/gh/norevi/waline-blobcatemojis@1.0/blobs/ablobcatrainbow.png">
<meta property="article:published_time" content="2023-08-12T13:53:52.000Z">
<meta property="article:modified_time" content="2024-03-10T11:50:36.099Z">
<meta property="article:author" content="sentry">
<meta property="article:tag" content="MySQL">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://gcore.jsdelivr.net/gh/norevi/waline-blobcatemojis@1.0/blobs/ablobcatrainbow.png">
  
  
  
  <meta name="keywords" content="MySQL">

  <!-- feed -->
  

  <link rel="stylesheet" href="/css/main.css?v=1.29.1">

  
    <link rel="shortcut icon" href="https://foruda.gitee.com/avatar/1677090168613487741/7674882_coclong_1619494197.png!avatar200">
  

  

  
</head>
<body>

<div class="l_body s:aa content tech" id="start" layout="post" ><aside class="l_left"><div class="leftbar-container">


<header class="header"><div class="logo-wrap"><a class="avatar" href="/about/"><div class="bg" style="opacity:0;background-image:url(https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/avatar/round/rainbow64@3x.webp);"></div><img no-lazy class="avatar" src="https://q1.qlogo.cn/g?b=qq&nk=2930811148&s=100" onerror="javascript:this.classList.add('error');this.src='https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/image/2659360.svg';"></a><a class="title" href="/"><div class="main" ff="title">Sentry's Blog</div><div class="sub normal cap">被人记住的感觉真好啊</div><div class="sub hover cap" style="opacity:0">孤独伴我同行</div></a></div></header>

<div class="nav-area">
<div class="search-wrapper" id="search-wrapper"><form class="search-form"><a class="search-button" onclick="document.getElementById(&quot;search-input&quot;).focus();"><svg t="1705074644177" viewBox="0 0 1025 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="1560" width="200" height="200"><path d="M1008.839137 935.96571L792.364903 719.491476a56.783488 56.783488 0 0 0-80.152866 0 358.53545 358.53545 0 1 1 100.857314-335.166073 362.840335 362.840335 0 0 1-3.689902 170.145468 51.248635 51.248635 0 1 0 99.217358 26.444296 462.057693 462.057693 0 1 0-158.255785 242.303546l185.930047 185.725053a51.248635 51.248635 0 0 0 72.568068 0 51.248635 51.248635 0 0 0 0-72.978056z" p-id="1561"></path><path d="M616.479587 615.969233a50.428657 50.428657 0 0 0-61.498362-5.534852 174.655348 174.655348 0 0 1-177.525271 3.484907 49.403684 49.403684 0 0 0-58.833433 6.76482l-3.074918 2.869923a49.403684 49.403684 0 0 0 8.609771 78.10292 277.767601 277.767601 0 0 0 286.992355-5.739847 49.403684 49.403684 0 0 0 8.404776-76.667958z" p-id="1562"></path></svg></a><input type="text" class="search-input" id="search-input" placeholder="站内搜索"></form><div id="search-result"></div><div class="search-no-result">没有找到内容！</div></div>


<nav class="menu dis-select"><a class="nav-item active" title="文章" href="/" style="color:#1BCDFC"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="currentColor" fill-rule="evenodd" d="M5.879 2.879C5 3.757 5 5.172 5 8v8c0 2.828 0 4.243.879 5.121C6.757 22 8.172 22 11 22h2c2.828 0 4.243 0 5.121-.879C19 20.243 19 18.828 19 16V8c0-2.828 0-4.243-.879-5.121C17.243 2 15.828 2 13 2h-2c-2.828 0-4.243 0-5.121.879M8.25 17a.75.75 0 0 1 .75-.75h3a.75.75 0 0 1 0 1.5H9a.75.75 0 0 1-.75-.75M9 12.25a.75.75 0 0 0 0 1.5h6a.75.75 0 0 0 0-1.5zM8.25 9A.75.75 0 0 1 9 8.25h6a.75.75 0 0 1 0 1.5H9A.75.75 0 0 1 8.25 9" clip-rule="evenodd"/><path fill="currentColor" d="M5.235 4.058C5 4.941 5 6.177 5 8v8c0 1.823 0 3.058.235 3.942L5 19.924c-.975-.096-1.631-.313-2.121-.803C2 18.243 2 16.828 2 14v-4c0-2.829 0-4.243.879-5.121c.49-.49 1.146-.707 2.121-.803zm13.53 15.884C19 19.058 19 17.822 19 16V8c0-1.823 0-3.059-.235-3.942l.235.018c.975.096 1.631.313 2.121.803C22 5.757 22 7.17 22 9.999v4c0 2.83 0 4.243-.879 5.122c-.49.49-1.146.707-2.121.803z" opacity=".5"/></svg></a><a class="nav-item" title="项目" href="/wiki/" style="color:#3DC550"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="currentColor" fill-rule="evenodd" d="M14.25 4.48v3.057c0 .111 0 .27.02.406a.936.936 0 0 0 .445.683a.96.96 0 0 0 .783.072c.13-.04.272-.108.378-.159L17 8.005l1.124.534c.106.05.248.119.378.16a.958.958 0 0 0 .783-.073a.936.936 0 0 0 .444-.683c.021-.136.021-.295.021-.406V3.031c.113-.005.224-.01.332-.013C21.154 2.98 22 3.86 22 4.933v11.21c0 1.112-.906 2.01-2.015 2.08c-.97.06-2.108.179-2.985.41c-1.082.286-1.99 1.068-3.373 1.436c-.626.167-1.324.257-1.627.323V5.174c.32-.079 1.382-.203 1.674-.371c.184-.107.377-.216.576-.323m5.478 8.338a.75.75 0 0 1-.546.91l-4 1a.75.75 0 0 1-.364-1.456l4-1a.75.75 0 0 1 .91.546" clip-rule="evenodd"/><path fill="currentColor" d="M18.25 3.151c-.62.073-1.23.18-1.75.336a8.2 8.2 0 0 0-.75.27v3.182l.75-.356l.008-.005a1.13 1.13 0 0 1 .492-.13c.047 0 .094.004.138.01c.175.029.315.1.354.12l.009.005l.749.356V3.647z"/><path fill="currentColor" d="M12 5.214c-.334-.064-1.057-.161-1.718-.339C8.938 4.515 8.05 3.765 7 3.487c-.887-.234-2.041-.352-3.018-.412C2.886 3.007 2 3.9 2 4.998v11.146c0 1.11.906 2.01 2.015 2.079c.97.06 2.108.179 2.985.41c.486.129 1.216.431 1.873.726c1.005.451 2.052.797 3.127 1.034z" opacity=".5"/><path fill="currentColor" d="M4.273 12.818a.75.75 0 0 1 .91-.545l4 1a.75.75 0 1 1-.365 1.455l-4-1a.75.75 0 0 1-.545-.91m.909-4.545a.75.75 0 1 0-.364 1.455l4 1a.75.75 0 0 0 .364-1.455z"/></svg></a><a class="nav-item" title="便笺" href="/notes/" style="color:#FA6400"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="currentColor" d="M20 12a8 8 0 1 1-16 0a8 8 0 0 1 16 0" opacity=".5"/><path fill="currentColor" d="M17.712 5.453c1.047-.193 2.006-.259 2.797-.152c.77.103 1.536.393 1.956 1.064c.446.714.312 1.542-.012 2.258c-.33.728-.918 1.499-1.672 2.268c-1.516 1.547-3.836 3.226-6.597 4.697c-2.763 1.472-5.495 2.484-7.694 2.92c-1.095.217-2.098.299-2.923.201c-.8-.095-1.6-.383-2.032-1.075c-.47-.752-.296-1.63.07-2.379c.375-.768 1.032-1.586 1.872-2.403L4 12.416c0 .219.083.71.168 1.146c.045.23.09.444.123.596c-.652.666-1.098 1.263-1.339 1.756c-.277.567-.208.825-.145.925c.072.116.305.305.937.38c.609.073 1.44.018 2.455-.183c2.02-.4 4.613-1.351 7.28-2.772c2.667-1.42 4.85-3.015 6.23-4.423c.694-.707 1.15-1.334 1.377-1.836c.233-.515.167-.75.107-.844c-.07-.112-.289-.294-.883-.374c-.542-.072-1.272-.041-2.163.112L16.87 5.656c.338-.101.658-.17.842-.203"/></svg></a><a class="nav-item" title="更多" href="/more/" style="color:#F44336"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="currentColor" d="m13.629 20.472l-.542.916c-.483.816-1.69.816-2.174 0l-.542-.916c-.42-.71-.63-1.066-.968-1.262c-.338-.197-.763-.204-1.613-.219c-1.256-.021-2.043-.098-2.703-.372a5 5 0 0 1-2.706-2.706C2 14.995 2 13.83 2 11.5v-1c0-3.273 0-4.91.737-6.112a5 5 0 0 1 1.65-1.651C5.59 2 7.228 2 10.5 2h3c3.273 0 4.91 0 6.113.737a5 5 0 0 1 1.65 1.65C22 5.59 22 7.228 22 10.5v1c0 2.33 0 3.495-.38 4.413a5 5 0 0 1-2.707 2.706c-.66.274-1.447.35-2.703.372c-.85.015-1.275.022-1.613.219c-.338.196-.548.551-.968 1.262" opacity=".5"/><path fill="currentColor" d="M10.99 14.308c-1.327-.978-3.49-2.84-3.49-4.593c0-2.677 2.475-3.677 4.5-1.609c2.025-2.068 4.5-1.068 4.5 1.609c0 1.752-2.163 3.615-3.49 4.593c-.454.335-.681.502-1.01.502c-.329 0-.556-.167-1.01-.502"/></svg></a></nav>
</div>
<div class="widgets">


<widget class="widget-wrapper post-list"><div class="widget-header dis-select"><span class="name">最近更新</span></div><div class="widget-body fs14"><a class="item title" href="/2023/07/16/base/%E5%86%99%E4%BD%9C%E5%A7%BF%E5%8A%BF/"><span class="title">写作姿势</span></a><a class="item title" href="/2023/07/11/base/%E5%B8%B8%E7%94%A8%E8%BD%AF%E4%BB%B6/"><span class="title">常用软件</span></a><a class="item title" href="/2023/07/28/base/%E5%8D%9A%E5%AE%A2%E7%BE%8E%E5%8C%96/"><span class="title">博客美化</span></a><a class="item title" href="/2024/04/27/other/%E7%BD%91%E7%BB%9C%E6%97%A5%E5%BF%97/"><span class="title">一周是一年的2%</span></a><a class="item title" href="/2023/08/09/other/%E5%A4%A7%E5%AD%A6%E4%B8%8A%E5%88%B0%E7%8E%B0%E5%9C%A8%E7%BB%88%E4%BA%8E%E6%98%8E%E7%99%BD%E7%9A%84%E4%BA%8B/"><span class="title">大学上到现在终于明白的事</span></a><a class="item title" href="/2023/08/13/study/Java%E4%BB%A3%E7%A0%81%E7%B2%BE%E7%AE%80%E6%8C%87%E5%8D%97/"><span class="title">Java代码精简指南</span></a><a class="item title" href="/2023/06/18/base/Hexo%E6%90%AD%E5%BB%BA%E5%8D%9A%E5%AE%A2%E4%B9%8B%E8%B7%AF/"><span class="title">Hexo搭建博客之路</span></a><a class="item title" href="/2023/09/07/other/%E6%B8%B8%E9%BE%99%E5%BD%93%E5%BD%92%E6%B5%B7/"><span class="title">游龙当归海，海不迎我自来也</span></a><a class="item title" href="/2023/10/02/other/%E8%80%83%E9%A9%BE%E7%85%A7%E7%9A%84%E9%82%A3%E4%BA%9B%E4%BA%8B%E5%84%BF/"><span class="title">考驾照的那些事儿</span></a><a class="item title" href="/2024/01/15/other/%E9%80%89%E6%8B%A9/"><span class="title">找工作</span></a></div></widget>
</div>
<footer class="footer dis-select"><div class="social-wrap"><a class="social" href="https://gitee.com/coclong" target="_blank" rel="external nofollow noopener noreferrer"><img class="lazy" src="" data-src="https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/social/08a41b181ce68.svg"/></a><a class="social" href="https://wpa.qq.com/msgrd?v=3&uin=2930811148&site=qq&menu=yes&jumpflag=1" target="_blank" rel="external nofollow noopener noreferrer"><img class="lazy" src="" data-src="/images/footer/qq32.svg"/></a><a class="social" href="/" rel="noopener noreferrer"><img class="lazy" src="" data-src="https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/social/3616429.svg"/></a><a class="social" href="/more" rel="noopener noreferrer"><img class="lazy" src="" data-src="https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/social/942ebbf1a4b91.svg"/></a></div></footer>
</div></aside><div class="l_main" id="main">





<div class="article banner top">
  <div class="content">
    <div class="top bread-nav footnote"><div class="left"><div class="flex-row" id="breadcrumb"><a class="cap breadcrumb" href="/">主页</a>
<span class="sep"></span><a class="cap breadcrumb" href="/">文章</a><span class="sep"></span><a class="cap breadcrumb-link" href="/categories/%E7%BB%83%E4%B9%A0/">练习</a></div>
<div class="flex-row" id="post-meta"><span class="text created">发布于：<time datetime="2023-08-12T13:53:52.000Z">2023-08-12</time></span><span class="sep updated"></span><span class="text updated">更新于：<time datetime="2024-03-10T11:50:36.099Z">2024-03-10</time></span></div></div></div>
    
    <div class="bottom only-title">
      
      <div class="text-area">
        <h1 class="text title"><span>SQL练习</span></h1>
        
      </div>
    </div>
    
  </div>
  </div><article class="md-text content"><a class="tag-plugin colorful hashtag" color="blue" target="_blank" rel="noopener" href="https://www.mysql.com/"><svg t="1701408144765" class="icon" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="4228" width="200" height="200"><path d="M426.6 64.8c34.8 5.8 58.4 38.8 52.6 73.6l-19.6 117.6h190.2l23-138.6c5.8-34.8 38.8-58.4 73.6-52.6s58.4 38.8 52.6 73.6l-19.4 117.6H896c35.4 0 64 28.6 64 64s-28.6 64-64 64h-137.8l-42.6 256H832c35.4 0 64 28.6 64 64s-28.6 64-64 64h-137.8l-23 138.6c-5.8 34.8-38.8 58.4-73.6 52.6s-58.4-38.8-52.6-73.6l19.6-117.4h-190.4l-23 138.6c-5.8 34.8-38.8 58.4-73.6 52.6s-58.4-38.8-52.6-73.6l19.4-117.8H128c-35.4 0-64-28.6-64-64s28.6-64 64-64h137.8l42.6-256H192c-35.4 0-64-28.6-64-64s28.6-64 64-64h137.8l23-138.6c5.8-34.8 38.8-58.4 73.6-52.6z m11.6 319.2l-42.6 256h190.2l42.6-256h-190.2z" p-id="4229"></path></svg><span>MySQL</span></a>

<h2 id="练习题"><a href="#练习题" class="headerlink" title="练习题"></a>练习题</h2><p>与同学闲聊之余，知道了这一次的 MySQL 练习题，闲暇之余将前 18 道题完成了（按照作者的说法难度是依次递增的）<span class="tag-plugin emoji"><img no-lazy="" class="inline lazy" src="" data-src="https://gcore.jsdelivr.net/gh/norevi/waline-blobcatemojis@1.0/blobs/ablobcatrainbow.png"/></span>。我使用的是 MySQL 8.0.33，练习题中用到了一些 8.0 的新特性</p>
<div class="tag-plugin link dis-select"><a class="link-card rich" title="" href="https://www.jianshu.com/p/476b52ee4f1b" target="_blank" rel="external nofollow noopener noreferrer" cardlink autofill="title,icon,desc"><div class="top"><div class="lazy img" data-bg="https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/link/8f277b4ee0ecd.svg"></div><span class="cap link footnote">https://www.jianshu.com/p/476b52ee4f1b</span></div><div class="bottom"><span class="title">https://www.jianshu.com/p/476b52ee4f1b</span><span class="cap desc footnote"></span></div></a></div>

<hr>
<ol>
<li><p>查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t3.<span class="operator">*</span>, t1.score, t2.score</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line">         <span class="keyword">join</span> student t3 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t3.SId</span><br><span class="line"><span class="keyword">where</span> t1.CId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span></span><br><span class="line">  <span class="keyword">and</span> t2.CId <span class="operator">=</span> <span class="string">&#x27;02&#x27;</span></span><br><span class="line">  <span class="keyword">and</span> t1.score <span class="operator">&gt;</span> t2.score;</span><br></pre></td></tr></table></figure>

<ul>
<li><p>查询同时存在” 01 “课程和” 02 “课程的情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">where</span> t1.CId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span></span><br><span class="line">  <span class="keyword">and</span> t2.CId <span class="operator">=</span> <span class="string">&#x27;02&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">left</span> <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId <span class="keyword">and</span> t2.CId <span class="operator">=</span> <span class="string">&#x27;02&#x27;</span></span><br><span class="line"><span class="keyword">where</span> t1.CId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询不存在” 01 “课程但存在” 02 “课程的情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc</span><br><span class="line"><span class="keyword">where</span> sc.SId <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> SId</span><br><span class="line">                     <span class="keyword">from</span> sc</span><br><span class="line">                     <span class="keyword">where</span> sc.CId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span>)</span><br><span class="line">  <span class="keyword">and</span> sc.CId <span class="operator">=</span> <span class="string">&#x27;02&#x27;</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(t1.score), t1.SId, t2.Sname</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> student t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">avg</span>(t1.score) <span class="operator">&gt;=</span> <span class="number">60</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询在 SC 表存在成绩的学生信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t2.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> student t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.SId, t1.Sname, <span class="built_in">count</span>(t2.SId), <span class="built_in">sum</span>(t2.score)</span><br><span class="line"><span class="keyword">from</span> student t1</span><br><span class="line">         <span class="keyword">left</span> <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId, t1.Sname;</span><br></pre></td></tr></table></figure>

<ul>
<li><p>查有成绩的学生信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> student t1</span><br><span class="line">         <span class="keyword">left</span> <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">where</span> t2.SId <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>查询「李」姓老师的数量</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line"><span class="keyword">from</span> teacher</span><br><span class="line"><span class="keyword">where</span> Tname <span class="keyword">like</span> <span class="string">&#x27;李%&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询学过「张三」老师授课的同学的信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> student t1</span><br><span class="line">         <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line">         <span class="keyword">join</span> course t3 <span class="keyword">on</span> t2.CId <span class="operator">=</span> t3.CId</span><br><span class="line">         <span class="keyword">join</span> teacher t4 <span class="keyword">on</span> t3.TId <span class="operator">=</span> t4.TId <span class="keyword">and</span> t4.Tname <span class="operator">=</span> <span class="string">&#x27;张三&#x27;</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t2.SId;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询没有学全所有课程的同学的信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>, <span class="built_in">count</span>(t2.sid)</span><br><span class="line"><span class="keyword">from</span> student t1</span><br><span class="line">         <span class="keyword">left</span> <span class="keyword">join</span> sc t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId</span><br><span class="line"><span class="keyword">having</span> <span class="built_in">count</span>(t2.SId) <span class="operator">&lt;</span> (<span class="keyword">select</span> <span class="built_in">count</span>(<span class="number">1</span>) <span class="keyword">from</span> course);</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t3.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc t2</span><br><span class="line">         <span class="keyword">join</span> student t3 <span class="keyword">on</span> t2.SId <span class="operator">=</span> t3.SId</span><br><span class="line"><span class="keyword">where</span> t2.cid <span class="keyword">in</span> (<span class="keyword">select</span> t1.CId <span class="keyword">from</span> sc t1 <span class="keyword">where</span> t1.SId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span>)</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t3.SId;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询和” 01 “号的同学学习的课程   完全相同的其他同学的信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> group_concat(t1.CId <span class="keyword">order</span> <span class="keyword">by</span> t1.CId) <span class="keyword">as</span> concat, t2.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> student t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">where</span> t1.SId <span class="operator">&lt;&gt;</span> <span class="string">&#x27;01&#x27;</span></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId</span><br><span class="line"><span class="keyword">having</span> concat <span class="operator">=</span> (<span class="keyword">select</span> group_concat(CId <span class="keyword">order</span> <span class="keyword">by</span> CId) <span class="keyword">from</span> sc <span class="keyword">where</span> SId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询没学过”张三”老师讲授的任一门课程的学生姓名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> student</span><br><span class="line"><span class="keyword">where</span> SId <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> t1.SId</span><br><span class="line">                  <span class="keyword">from</span> sc t1</span><br><span class="line">                           <span class="keyword">join</span> course t2 <span class="keyword">on</span> t1.CId <span class="operator">=</span> t2.CId</span><br><span class="line">                           <span class="keyword">join</span> teacher t3 <span class="keyword">on</span> t2.TId <span class="operator">=</span> t3.TId</span><br><span class="line">                  <span class="keyword">where</span> t3.Tname <span class="operator">=</span> <span class="string">&#x27;张三&#x27;</span></span><br><span class="line">                  <span class="keyword">group</span> <span class="keyword">by</span> t1.SId);</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.SId, t2.Sname, <span class="built_in">avg</span>(t1.score)</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> student t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">where</span> t1.SId <span class="keyword">in</span> (<span class="keyword">select</span> SId</span><br><span class="line">                 <span class="keyword">from</span> sc</span><br><span class="line">                 <span class="keyword">where</span> score <span class="operator">&lt;</span> <span class="number">60</span></span><br><span class="line">                 <span class="keyword">group</span> <span class="keyword">by</span> SId</span><br><span class="line">                 <span class="keyword">having</span> <span class="built_in">count</span>(SId) <span class="operator">&gt;=</span> <span class="number">2</span>)</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.SId;</span><br></pre></td></tr></table></figure>
</li>
<li><p>检索” 01 “课程分数小于 60，按分数降序排列的学生信息</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t2.<span class="operator">*</span>, t1.score</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> student t2 <span class="keyword">on</span> t1.SId <span class="operator">=</span> t2.SId</span><br><span class="line"><span class="keyword">where</span> t1.CId <span class="operator">=</span> <span class="string">&#x27;01&#x27;</span></span><br><span class="line">  <span class="keyword">and</span> t1.score <span class="operator">&lt;</span> <span class="number">60</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> t1.score <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.<span class="operator">*</span>, <span class="built_in">avg</span>(t1.score) <span class="keyword">over</span> (<span class="keyword">PARTITION</span> <span class="keyword">BY</span> t1.SId) <span class="keyword">as</span> a</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> a <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询各科成绩最高分、最低分和平均分：<br>以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为&gt;&#x3D;60，中等为：70-80，优良为：80-90，优秀为：&gt;&#x3D;90</p>
<p>要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.CId,</span><br><span class="line">       t2.Cname,</span><br><span class="line">       <span class="built_in">max</span>(t1.score),</span><br><span class="line">       <span class="built_in">min</span>(t1.score),</span><br><span class="line">       <span class="built_in">avg</span>(t1.score),</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&gt;=</span> <span class="number">60</span>, <span class="number">1</span>, <span class="number">0</span>)) <span class="operator">/</span> <span class="built_in">count</span>(<span class="operator">*</span>)                   <span class="keyword">as</span> 及格率,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&gt;=</span> <span class="number">70</span> <span class="keyword">and</span> t1.score <span class="operator">&lt;</span> <span class="number">80</span>, <span class="number">1</span>, <span class="number">0</span>)) <span class="operator">/</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">as</span> 中等率,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&gt;=</span> <span class="number">80</span> <span class="keyword">and</span> t1.score <span class="operator">&lt;</span> <span class="number">90</span>, <span class="number">1</span>, <span class="number">0</span>)) <span class="operator">/</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">as</span> 优良率,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&gt;=</span> <span class="number">90</span>, <span class="number">1</span>, <span class="number">0</span>)) <span class="operator">/</span> <span class="built_in">count</span>(<span class="operator">*</span>)                   <span class="keyword">as</span> 优秀率</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> course t2 <span class="keyword">on</span> t1.CId <span class="operator">=</span> t2.CId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.CId</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">desc</span>, t1.CId;</span><br></pre></td></tr></table></figure>
</li>
<li><p>按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CID, SID, score, <span class="built_in">rank</span>() <span class="keyword">over</span> (<span class="keyword">PARTITION</span> <span class="keyword">BY</span> CId <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span> ) <span class="keyword">as</span> rank1</span><br><span class="line"><span class="keyword">from</span> sc</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> CId, rank1;</span><br></pre></td></tr></table></figure>

<p>没看懂他的写法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> a.cid, a.sid, a.score, <span class="built_in">count</span>(b.score) <span class="operator">+</span> <span class="number">1</span> <span class="keyword">as</span> rank1</span><br><span class="line"><span class="keyword">from</span> sc <span class="keyword">as</span> a</span><br><span class="line">         <span class="keyword">left</span> <span class="keyword">join</span> sc <span class="keyword">as</span> b</span><br><span class="line">                   <span class="keyword">on</span> a.score <span class="operator">&lt;</span> b.score <span class="keyword">and</span> a.cid <span class="operator">=</span> b.cid</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> a.cid, a.sid, a.score</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> a.cid, rank1;</span><br></pre></td></tr></table></figure>

<ul>
<li><p>按各科成绩进行排序，并显示排名， Score 重复时合并名次</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> CID, SID, score, <span class="built_in">dense_rank</span>() <span class="keyword">over</span> (<span class="keyword">PARTITION</span> <span class="keyword">BY</span> CId <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span> ) <span class="keyword">as</span> rank1</span><br><span class="line"><span class="keyword">from</span> sc</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> CId, rank1;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>查询学生的总成绩，并进行排名，总分重复时保留名次空缺</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> SID, <span class="built_in">sum</span>(score), <span class="built_in">rank</span>() <span class="keyword">over</span> (<span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">sum</span>(score) <span class="keyword">desc</span>)</span><br><span class="line"><span class="keyword">from</span> sc</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> SId;</span><br></pre></td></tr></table></figure>

<ul>
<li><p>查询学生的总成绩，并进行排名，总分重复时不保留名次空缺</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> SID, <span class="built_in">sum</span>(score), <span class="built_in">dense_rank</span>() <span class="keyword">over</span> (<span class="keyword">order</span> <span class="keyword">by</span> <span class="built_in">sum</span>(score) <span class="keyword">desc</span>)</span><br><span class="line"><span class="keyword">from</span> sc</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> SId;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> t1.CId,</span><br><span class="line">       t2.Cname,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&lt;=</span> <span class="number">100</span> <span class="keyword">and</span> t1.score <span class="operator">&gt;</span> <span class="number">85</span>, <span class="number">1</span>, <span class="number">0</span>)) <span class="keyword">as</span> <span class="string">&#x27;100-85&#x27;</span>,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&lt;=</span> <span class="number">85</span> <span class="keyword">and</span> t1.score <span class="operator">&gt;</span> <span class="number">70</span>, <span class="number">1</span>, <span class="number">0</span>))  <span class="keyword">as</span> <span class="string">&#x27;85-70&#x27;</span>,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&lt;=</span> <span class="number">70</span> <span class="keyword">and</span> t1.score <span class="operator">&gt;</span> <span class="number">60</span>, <span class="number">1</span>, <span class="number">0</span>))  <span class="keyword">as</span> <span class="string">&#x27;70-60&#x27;</span>,</span><br><span class="line">       <span class="built_in">sum</span>(if(t1.score <span class="operator">&lt;=</span> <span class="number">60</span> <span class="keyword">and</span> t1.score <span class="operator">&gt;</span> <span class="number">0</span>, <span class="number">1</span>, <span class="number">0</span>))   <span class="keyword">as</span> <span class="string">&#x27;60-0&#x27;</span>,</span><br><span class="line">       <span class="built_in">count</span>(<span class="number">1</span>)</span><br><span class="line"><span class="keyword">from</span> sc t1</span><br><span class="line">         <span class="keyword">join</span> course t2 <span class="keyword">on</span> t1.CId <span class="operator">=</span> t2.CId</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> t1.CId, t2.Cname;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询各科成绩前三名的记录</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">with</span> a <span class="keyword">as</span> (<span class="keyword">select</span> SId, CId, score, <span class="built_in">rank</span>() <span class="keyword">over</span> (<span class="keyword">PARTITION</span> <span class="keyword">BY</span> CId <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span> ) <span class="keyword">as</span> rank1</span><br><span class="line">           <span class="keyword">from</span> sc)</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> a</span><br><span class="line"><span class="keyword">where</span> a.rank1 <span class="operator">&lt;=</span> <span class="number">3</span>;</span><br></pre></td></tr></table></figure></li>
</ol>
<h2 id="SQL-日期区间查询"><a href="#SQL-日期区间查询" class="headerlink" title="SQL 日期区间查询"></a>SQL 日期区间查询</h2><ul>
<li><p>日期范围获取每天（截止结束日期）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_ADD(<span class="string">&#x27;2021-04-04&#x27;</span>, <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> signed <span class="type">INTEGER</span>) <span class="keyword">DAY</span>) <span class="keyword">AS</span> `<span class="type">date</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> DATEDIFF(<span class="string">&#x27;2021-07-04&#x27;</span>, <span class="string">&#x27;2021-04-04&#x27;</span>) <span class="operator">+</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>日期范围获取每月（截止结束日期所在月）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(<span class="string">&#x27;2021-04-04&#x27;</span>, <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> signed <span class="type">INTEGER</span>) <span class="keyword">MONTH</span>), <span class="string">&#x27;%Y-%m&#x27;</span>) <span class="keyword">AS</span> `<span class="keyword">month</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> TIMESTAMPDIFF(<span class="keyword">MONTH</span>, <span class="string">&#x27;2021-04-04&#x27;</span>, <span class="string">&#x27;2021-07-04&#x27;</span>) <span class="operator">+</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>本周每天（截止今日）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(</span><br><span class="line">               DATE_ADD(DATE_SUB(NOW(), <span class="type">INTERVAL</span> WEEKDAY(NOW()) <span class="operator">+</span> <span class="number">0</span> <span class="keyword">DAY</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> signed <span class="type">INTEGER</span>)</span><br><span class="line">                        <span class="keyword">DAY</span>), <span class="string">&#x27;%Y-%m-%d&#x27;</span>) <span class="keyword">AS</span> `<span class="type">date</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;=</span> WEEKDAY(NOW());</span><br></pre></td></tr></table></figure>
</li>
<li><p>近7日每天（截止今日）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="number">6</span> <span class="keyword">DAY</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">DAY</span>),</span><br><span class="line">                   <span class="string">&#x27;%Y-%m-%d&#x27;</span>) <span class="keyword">AS</span> `<span class="type">date</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="number">7</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>本月每天（截止今日）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_ADD(DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="keyword">DAY</span>(NOW()) <span class="operator">+</span> <span class="number">1</span> <span class="keyword">DAY</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> signed <span class="type">INTEGER</span>)</span><br><span class="line">                <span class="keyword">DAY</span>) <span class="keyword">AS</span> `<span class="type">date</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="keyword">DAY</span>(NOW());</span><br></pre></td></tr></table></figure>
</li>
<li><p>近30日每天（截止今日）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="number">29</span> <span class="keyword">DAY</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">DAY</span>),</span><br><span class="line">                   <span class="string">&#x27;%Y-%m-%d&#x27;</span>) <span class="keyword">AS</span> `<span class="type">date</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="number">30</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>本季度每月（截止所在月）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(CONCAT(<span class="keyword">YEAR</span>(NOW()), <span class="string">&#x27;-0&#x27;</span>, ((QUARTER(NOW()) <span class="operator">-</span> <span class="number">1</span>) <span class="operator">*</span> <span class="number">3</span> <span class="operator">+</span> <span class="number">1</span>), <span class="string">&#x27;-01&#x27;</span>), <span class="type">INTERVAL</span></span><br><span class="line">                            <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">MONTH</span>), <span class="string">&#x27;%Y-%m&#x27;</span>) <span class="keyword">as</span> `<span class="keyword">month</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="keyword">MONTH</span>(NOW()) <span class="operator">%</span> <span class="number">3</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>近半年每月（截止所在月）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="number">5</span> <span class="keyword">MONTH</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">MONTH</span>),</span><br><span class="line">                   <span class="string">&#x27;%Y-%m&#x27;</span>) <span class="keyword">as</span> `<span class="keyword">month</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="number">6</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>本年每月（截止所在月）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(CONCAT(<span class="keyword">YEAR</span>(NOW()), <span class="string">&#x27;-01-01&#x27;</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">MONTH</span>),</span><br><span class="line">                   <span class="string">&#x27;%Y-%m&#x27;</span>) <span class="keyword">as</span> `<span class="keyword">month</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="keyword">MONTH</span>(NOW());</span><br></pre></td></tr></table></figure>
</li>
<li><p>近1年每月（截止所在月）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="operator">-</span> <span class="number">11</span> <span class="keyword">MONTH</span>), <span class="type">INTERVAL</span> <span class="built_in">CAST</span>(help_topic_id <span class="keyword">AS</span> SIGNED <span class="type">INTEGER</span>) <span class="keyword">MONTH</span>),</span><br><span class="line">                   <span class="string">&#x27;%Y-%m&#x27;</span>) <span class="keyword">as</span> `<span class="keyword">month</span>`</span><br><span class="line"><span class="keyword">FROM</span> mysql.help_topic</span><br><span class="line"><span class="keyword">WHERE</span> help_topic_id <span class="operator">&lt;</span> <span class="number">12</span>;</span><br></pre></td></tr></table></figure></li>
</ul>

<div class="article-footer fs14">
    <section id="references">
      <div class="header"><span>参考资料</span></div>
      <div class="body">
        <ul>
        <li class="post-title">
          <p><a target="_blank" rel="noopener" href="https://www.jianshu.com/p/476b52ee4f1b">50道SQL练习题及答案与详细分析</a></p>

        </li>
        
        <li class="post-title">
          <p><a target="_blank" rel="noopener" href="https://www.52pojie.cn/thread-1758687-1-1.html">MYSQL 获取本周、本月每天，本季度、近半年、本年、近一年的每个月份</a></p>

        </li>
        </ul>
      </div>
    </section>
    
    <section id="license">
      <div class="header"><span>许可协议</span></div>
      <div class="body"><p>本文采用 <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">署名-非商业性使用-相同方式共享 4.0 国际</a> 许可协议，转载请注明出处。</p>
</div>
    </section>
    </div>
</article>
<div class="related-wrap" id="read-next"><section class="body"><div class="item" id="prev"><div class="note">较新文章</div><a href="/2023/08/13/study/Java%E4%BB%A3%E7%A0%81%E7%B2%BE%E7%AE%80%E6%8C%87%E5%8D%97/">Java代码精简指南</a></div><div class="item" id="next"><div class="note">较早文章</div><a href="/2023/08/09/other/%E5%A4%A7%E5%AD%A6%E4%B8%8A%E5%88%B0%E7%8E%B0%E5%9C%A8%E7%BB%88%E4%BA%8E%E6%98%8E%E7%99%BD%E7%9A%84%E4%BA%8B/">大学上到现在终于明白的事</a></div></section></div>

<div class="related-wrap" id="related-posts"></div>




<footer class="page-footer footnote"><hr><div class="sitemap"><div class="sitemap-group"><span class="fs15">博客</span><a href="/">近期</a><a href="/categories/">分类</a><a href="/tags/">标签</a><a href="/archives/">归档</a></div><div class="sitemap-group"><span class="fs15">项目</span><a href="/wiki/">笔记</a><a target="_blank" rel="noopener" href="https://gitee.com/coclong">开源库</a></div><div class="sitemap-group"><span class="fs15">社交</span><a href="/notes/">友链</a><a href="/">留言板</a><a target="_blank" rel="noopener" href="https://space.bilibili.com/415988557">哔哩哔哩</a></div><div class="sitemap-group"><span class="fs15">更多</span><a href="/more">关于本站</a><a target="_blank" rel="noopener" href="https://gitee.com/coclong">Gitee</a><a target="_blank" rel="noopener" href="https://github.com/Godlike-long">GitHub</a></div></div><div class="text"><p>本站由 <a href="/">sentry</a> 使用 <a target="_blank" rel="noopener" href="https://github.com/xaoxuu/hexo-theme-stellar/tree/1.29.1">Stellar 1.29.1</a> 主题创建。<br>本博客所有文章除特别声明外，均采用 <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> 许可协议，转载请注明出处。</p>
</div></footer>
<div class="main-mask" onclick="sidebar.dismiss()"></div></div><aside class="l_right">
<div class="widgets">



<widget class="widget-wrapper toc" id="data-toc" collapse="false"><div class="widget-header dis-select"><span class="name">本文目录</span><a class="cap-action" onclick="sidebar.toggleTOC()" ><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="none" stroke="currentColor" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M10 6h11m-11 6h11m-11 6h11M4 6h1v4m-1 0h2m0 8H4c0-1 2-2 2-3s-1-1.5-2-1"/></svg></a></div><div class="widget-body"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%BB%83%E4%B9%A0%E9%A2%98"><span class="toc-text">练习题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL-%E6%97%A5%E6%9C%9F%E5%8C%BA%E9%97%B4%E6%9F%A5%E8%AF%A2"><span class="toc-text">SQL 日期区间查询</span></a></li></ol></div><div class="widget-footer">

<a class="top" onclick="util.scrollTop()"><svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><g fill="none" stroke="currentColor" stroke-width="1.5"><path d="M2 12c0-4.714 0-7.071 1.464-8.536C4.93 2 7.286 2 12 2c4.714 0 7.071 0 8.535 1.464C22 4.93 22 7.286 22 12c0 4.714 0 7.071-1.465 8.535C19.072 22 16.714 22 12 22s-7.071 0-8.536-1.465C2 19.072 2 16.714 2 12Z"/><path stroke-linecap="round" stroke-linejoin="round" d="m9 15.5l3-3l3 3m-6-4l3-3l3 3"/></g></svg><span>回到顶部</span></a></div></widget>
</div></aside><div class='float-panel blur'>
  <button type='button' style='display:none' class='laptop-only rightbar-toggle mobile' onclick='sidebar.rightbar()'>
    <svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><path fill="none" stroke="currentColor" stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M10 6h11m-11 6h11m-11 6h11M4 6h1v4m-1 0h2m0 8H4c0-1 2-2 2-3s-1-1.5-2-1"/></svg>
  </button>
  <button type='button' style='display:none' class='mobile-only leftbar-toggle mobile' onclick='sidebar.leftbar()'>
    <svg xmlns="http://www.w3.org/2000/svg" width="32" height="32" viewBox="0 0 24 24"><g fill="none" stroke="currentColor" stroke-width="1.5"><path d="M2 11c0-3.771 0-5.657 1.172-6.828C4.343 3 6.229 3 10 3h4c3.771 0 5.657 0 6.828 1.172C22 5.343 22 7.229 22 11v2c0 3.771 0 5.657-1.172 6.828C19.657 21 17.771 21 14 21h-4c-3.771 0-5.657 0-6.828-1.172C2 18.657 2 16.771 2 13z"/><path id="sep" stroke-linecap="round" d="M5.5 10h6m-5 4h4m4.5 7V3"/></g></svg>
  </button>
</div>
</div><div class="scripts">
<script type="text/javascript">
  const ctx = {
    date_suffix: {
      just: `刚刚`,
      min: `分钟前`,
      hour: `小时前`,
      day: `天前`,
    },
    root : `/`,
  };

  // required plugins (only load if needs)
  if (`local_search`) {
    ctx.search = {};
    ctx.search.service = `local_search`;
    if (ctx.search.service == 'local_search') {
      let service_obj = Object.assign({}, `{"field":"all","path":"/search.json","content":true,"sort":"-date"}`);
      ctx.search[ctx.search.service] = service_obj;
    }
  }
  const def = {
    avatar: `https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/avatar/round/3442075.svg`,
    cover: `https://gcore.jsdelivr.net/gh/cdn-x/placeholder@1.0.12/cover/76b86c0226ffd.svg`,
  };
  const deps = {
    jquery: `https://cdn.jsdelivr.net/npm/jquery@3.7.1/dist/jquery.min.js`,
    marked: `https://cdn.jsdelivr.net/npm/marked@13.0.1/lib/marked.umd.min.js`
  }
  

</script>

<script type="text/javascript">
  const utils = {
    // 懒加载 css https://github.com/filamentgroup/loadCSS
    css: (href, before, media, attributes) => {
      var doc = window.document;
      var ss = doc.createElement("link");
      var ref;
      if (before) {
        ref = before;
      } else {
        var refs = (doc.body || doc.getElementsByTagName("head")[0]).childNodes;
        ref = refs[refs.length - 1];
      }
      var sheets = doc.styleSheets;
      if (attributes) {
        for (var attributeName in attributes) {
          if (attributes.hasOwnProperty(attributeName)) {
            ss.setAttribute(attributeName, attributes[attributeName]);
          }
        }
      }
      ss.rel = "stylesheet";
      ss.href = href;
      ss.media = "only x";
      function ready(cb) {
        if (doc.body) {
          return cb();
        }
        setTimeout(function () {
          ready(cb);
        });
      }
      ready(function () {
        ref.parentNode.insertBefore(ss, before ? ref : ref.nextSibling);
      });
      var onloadcssdefined = function (cb) {
        var resolvedHref = ss.href;
        var i = sheets.length;
        while (i--) {
          if (sheets[i].href === resolvedHref) {
            return cb();
          }
        }
        setTimeout(function () {
          onloadcssdefined(cb);
        });
      };
      function loadCB() {
        if (ss.addEventListener) {
          ss.removeEventListener("load", loadCB);
        }
        ss.media = media || "all";
      }
      if (ss.addEventListener) {
        ss.addEventListener("load", loadCB);
      }
      ss.onloadcssdefined = onloadcssdefined;
      onloadcssdefined(loadCB);
      return ss;
    },

    js: (src, opt) => new Promise((resolve, reject) => {
      var script = document.createElement('script');
      if (src.startsWith('/')){
        src = ctx.root + src.substring(1);
      }
      script.src = src;
      if (opt) {
        for (let key of Object.keys(opt)) {
          script[key] = opt[key]
        }
      } else {
        // 默认异步，如果需要同步，第二个参数传入 {} 即可
        script.async = true
      }
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    }),

    jq: (fn) => {
      if (typeof jQuery === 'undefined') {
        utils.js(deps.jquery).then(fn)
      } else {
        fn()
      }
    },
    
    onLoading: (el) => {
      if (el) {
        $(el).append('<div class="loading-wrap"><svg xmlns="http://www.w3.org/2000/svg" width="2em" height="2em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 24 24"><g fill="none" stroke="currentColor" stroke-linecap="round" stroke-width="2"><path stroke-dasharray="60" stroke-dashoffset="60" stroke-opacity=".3" d="M12 3C16.9706 3 21 7.02944 21 12C21 16.9706 16.9706 21 12 21C7.02944 21 3 16.9706 3 12C3 7.02944 7.02944 3 12 3Z"><animate fill="freeze" attributeName="stroke-dashoffset" dur="1.3s" values="60;0"/></path><path stroke-dasharray="15" stroke-dashoffset="15" d="M12 3C16.9706 3 21 7.02944 21 12"><animate fill="freeze" attributeName="stroke-dashoffset" dur="0.3s" values="15;0"/><animateTransform attributeName="transform" dur="1.5s" repeatCount="indefinite" type="rotate" values="0 12 12;360 12 12"/></path></g></svg></div>');
      }
    },
    onLoadSuccess: (el) => {
      if (el) {
        $(el).find('.loading-wrap').remove();
      }
    },
    onLoadFailure: (el) => {
      if (el) {
        $(el).find('.loading-wrap svg').remove();
        $(el).find('.loading-wrap').append('<svg xmlns="http://www.w3.org/2000/svg" width="2em" height="2em" preserveAspectRatio="xMidYMid meet" viewBox="0 0 24 24"><g fill="none" stroke="currentColor" stroke-linecap="round" stroke-linejoin="round" stroke-width="2"><path stroke-dasharray="60" stroke-dashoffset="60" d="M12 3L21 20H3L12 3Z"><animate fill="freeze" attributeName="stroke-dashoffset" dur="0.5s" values="60;0"/></path><path stroke-dasharray="6" stroke-dashoffset="6" d="M12 10V14"><animate fill="freeze" attributeName="stroke-dashoffset" begin="0.6s" dur="0.2s" values="6;0"/></path></g><circle cx="12" cy="17" r="1" fill="currentColor" fill-opacity="0"><animate fill="freeze" attributeName="fill-opacity" begin="0.8s" dur="0.4s" values="0;1"/></circle></svg>');
        $(el).find('.loading-wrap').addClass('error');
      }
    },
    request: (el, url, callback, onFailure) => {
      let retryTimes = 3;
      utils.onLoading(el);
      function req() {
        return new Promise((resolve, reject) => {
          let status = 0; // 0 等待 1 完成 2 超时
          let timer = setTimeout(() => {
            if (status === 0) {
              status = 2;
              timer = null;
              reject('请求超时');
              if (retryTimes == 0) {
                onFailure();
              }
            }
          }, 5000);
          fetch(url).then(function(response) {
            if (status !== 2) {
              clearTimeout(timer);
              resolve(response);
              timer = null;
              status = 1;
            }
            if (response.ok) {
              return response.json();
            }
            throw new Error('Network response was not ok.');
          }).then(function(data) {
            retryTimes = 0;
            utils.onLoadSuccess(el);
            callback(data);
          }).catch(function(error) {
            if (retryTimes > 0) {
              retryTimes -= 1;
              setTimeout(() => {
                req();
              }, 5000);
            } else {
              utils.onLoadFailure(el);
              onFailure();
            }
          });
        });
      }
      req();
    },
  };
</script>

<script>
  const sidebar = {
    leftbar: () => {
      if (l_body) {
        l_body.toggleAttribute('leftbar');
        l_body.removeAttribute('rightbar');
      }
    },
    rightbar: () => {
      if (l_body) {
        l_body.toggleAttribute('rightbar');
        l_body.removeAttribute('leftbar');
      }
    },
    dismiss: () => {
      if (l_body) {
        l_body.removeAttribute('leftbar');
        l_body.removeAttribute('rightbar');
      }
    },
    toggleTOC: () => {
      document.querySelector('#data-toc').classList.toggle('collapse');
    }
  }
</script>
<script type="text/javascript">
  (() => {
    const tagSwitchers = document.querySelectorAll('.tag-subtree.parent-tag > a > .tag-switcher-wrapper')
    for (const tagSwitcher of tagSwitchers) {
      tagSwitcher.addEventListener('click', (e) => {
        const parent = e.target.closest('.tag-subtree.parent-tag')
        parent.classList.toggle('expanded')
        e.preventDefault()
      })
    }

    // Get active tag from query string, then activate it.
    const urlParams = new URLSearchParams(window.location.search)
    const activeTag = urlParams.get('tag')
    if (activeTag) {
      let tag = document.querySelector(`.tag-subtree[data-tag="${activeTag}"]`)
      if (tag) {
        tag.querySelector('a').classList.add('active')
        
        while (tag) {
          tag.classList.add('expanded')
          tag = tag.parentElement.closest('.tag-subtree.parent-tag')
        }
      }
    }
  })()
</script>


<!-- required -->
<script src="/js/main.js?v=1.29.1" defer></script>

<script type="text/javascript">
  const applyTheme = (theme) => {
    if (theme === 'auto') {
      document.documentElement.removeAttribute('data-theme')
    } else {
      document.documentElement.setAttribute('data-theme', theme)
    }

    applyThemeToGiscus(theme)
  }

  const applyThemeToGiscus = (theme) => {
    theme = theme === 'auto' ? 'preferred_color_scheme' : theme

    const cmt = document.getElementById('giscus')
    if (cmt) {
      // This works before giscus load.
      cmt.setAttribute('data-theme', theme)
    }

    const iframe = document.querySelector('#comments > section.giscus > iframe')
    if (iframe) {
      // This works after giscus loaded.
      const src = iframe.src
      const newSrc = src.replace(/theme=[\w]+/, `theme=${theme}`)
      iframe.src = newSrc
    }
  }

  const switchTheme = () => {
    // light -> dark -> auto -> light -> ...
    const currentTheme = document.documentElement.getAttribute('data-theme')
    let newTheme;
    switch (currentTheme) {
      case 'light':
        newTheme = 'dark'
        break
      case 'dark':
        newTheme = 'auto'
        break
      default:
        newTheme = 'light'
    }
    applyTheme(newTheme)
    window.localStorage.setItem('Stellar.theme', newTheme)

    const messages = {
      light: `切换到浅色模式`,
      dark: `切换到深色模式`,
      auto: `切换到跟随系统配色`,
    }
    hud?.toast?.(messages[newTheme])
  }

  (() => {
    // Apply user's preferred theme, if any.
    const theme = window.localStorage.getItem('Stellar.theme')
    if (theme !== null) {
      applyTheme(theme)
    }
  })()
</script>


<!-- optional -->



<script defer>
  window.addEventListener('DOMContentLoaded', (event) => {
    ctx.services = Object.assign({}, JSON.parse(`{"mdrender":{"js":"/js/services/mdrender.js"},"siteinfo":{"js":"/js/services/siteinfo.js","api":null},"ghinfo":{"js":"/js/services/ghinfo.js"},"sites":{"js":"/js/services/sites.js"},"friends":{"js":"/js/services/friends.js"},"timeline":{"js":"/js/services/timeline.js"},"fcircle":{"js":"/js/services/fcircle.js"},"weibo":{"js":"/js/services/weibo.js"},"memos":{"js":"/js/services/memos.js"}}`));
    for (let id of Object.keys(ctx.services)) {
      const js = ctx.services[id].js;
      if (id == 'siteinfo') {
        ctx.cardlinks = document.querySelectorAll('a.link-card[cardlink]');
        if (ctx.cardlinks?.length > 0) {
          utils.js(js, { defer: true }).then(function () {
            setCardLink(ctx.cardlinks);
          });
        }
      } else {
        const els = document.getElementsByClassName(`ds-${id}`);
        if (els?.length > 0) {
          utils.jq(() => {
            if (id == 'timeline' || 'memos' || 'marked') {
              utils.js(deps.marked).then(function () {
                utils.js(js, { defer: true });
              });
            } else {
              utils.js(js, { defer: true });
            }
          });
        }
      }
    }
  });
</script>

<script>
  window.addEventListener('DOMContentLoaded', (event) => {
    ctx.search = {
      path: `/search.json`,
    }
    utils.js('/js/search/local-search.js', { defer: true });
  });
</script><script>
  window.FPConfig = {
    delay: 0,
    ignoreKeywords: [],
    maxRPS: 5,
    hoverDelay: 25
  };
</script>
<script defer src="https://cdn.jsdelivr.net/npm/flying-pages@2/flying-pages.min.js"></script><script defer src="https://cdn.jsdelivr.net/npm/vanilla-lazyload@19.1.3/dist/lazyload.min.js"></script>
<script>
  // https://www.npmjs.com/package/vanilla-lazyload
  // Set the options globally
  // to make LazyLoad self-initialize
  window.lazyLoadOptions = {
    elements_selector: ".lazy",
  };
  // Listen to the initialization event
  // and get the instance of LazyLoad
  window.addEventListener(
    "LazyLoad::Initialized",
    function (event) {
      window.lazyLoadInstance = event.detail.instance;
    },
    false
  );
  document.addEventListener('DOMContentLoaded', function () {
    window.lazyLoadInstance?.update();
  });
</script><script>
  ctx.fancybox = {
    selector: `.timenode p>img`,
    css: `https://cdn.jsdelivr.net/npm/@fancyapps/ui@5.0/dist/fancybox/fancybox.css`,
    js: `https://cdn.jsdelivr.net/npm/@fancyapps/ui@5.0/dist/fancybox/fancybox.umd.js`
  };
  var selector = '[data-fancybox]:not(.error)';
  if (ctx.fancybox.selector) {
    selector += `, ${ctx.fancybox.selector}`
  }
  var needFancybox = document.querySelectorAll(selector).length !== 0;
  if (!needFancybox) {
    const els = document.getElementsByClassName('ds-memos');
    if (els != undefined && els.length > 0) {
      needFancybox = true;
    }
  }
  if (needFancybox) {
    utils.css(ctx.fancybox.css);
    utils.js(ctx.fancybox.js, { defer: true }).then(function () {
      Fancybox.bind(selector, {
        hideScrollbar: false,
        Thumbs: {
          autoStart: false,
        },
        caption: (fancybox, slide) => {
          return slide.triggerEl.alt || slide.triggerEl.dataset.caption || null
        }
      });
    })
  }
</script>
<script>
  window.addEventListener('DOMContentLoaded', (event) => {
    const swiper_api = document.getElementById('swiper-api');
    if (swiper_api != undefined) {
      utils.css(`https://unpkg.com/swiper@10.3.1/swiper-bundle.min.css`);
      utils.js(`https://unpkg.com/swiper@10.3.1/swiper-bundle.min.js`, { defer: true }).then(function () {
        const effect = swiper_api.getAttribute('effect') || '';
        var swiper = new Swiper('.swiper#swiper-api', {
          slidesPerView: 'auto',
          spaceBetween: 8,
          centeredSlides: true,
          effect: effect,
          rewind: true,
          pagination: {
            el: '.swiper-pagination',
            clickable: true,
          },
          navigation: {
            nextEl: '.swiper-button-next',
            prevEl: '.swiper-button-prev',
          },
        });
      })
    }
  });
</script>
<script>
  document.addEventListener('DOMContentLoaded', function () {
    window.codeElements = document.querySelectorAll('.code');
    if (window.codeElements.length > 0) {
      ctx.copycode = {
        default_text: `Copy`,
        success_text: `Copied`,
        toast: `复制成功`,
      };
      utils.js('/js/plugins/copycode.js');
    }
  });
</script>


<!-- inject -->

</div></body></html>
